--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('NOM_ARTENT');

 DELETE FROM se_NOM_ARTENT;


/*delete from &erp.NOM_ARTENT ss  where exists (
select * from (
SELECT &outils.stragg(source) source, combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
FROM (
  SELECT 'uv' source ,g.* FROM se_NOM_ARTENT g where unfab_cod != 'ETUDE'
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.NOM_ARTENT g where unfab_cod != 'ETUDE'
  )
where unfab_cod !='ETUDE'
GROUP BY combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
HAVING Count(1)!=2 ) r where ss.enreg_id=r.enreg_id
                        and not exists (select 1 from &erp.NOM_ARTlig l where l.pere_gene_cod=r.gene_cod and l.pere_gene_ind=r.gene_ind and l.pere_unfab_cod=r.unfab_cod))
ORDER BY gene_cod,gene_ind*/

/*UPDATE &erp.NOM_ARTENT  e SET enreg_s='L' WHERE EXISTS (SELECT 1 FROM temp_ecart_nomentsfa t WHERE t.enreg_id=e.enreg_id)
AND erp_mli2.isnumeric(GENE_COD)=1 */

/*nomenclatures de  de progres*/
 INSERT INTO se_NOM_ARTENT
 (SELECT DISTINCT Nvl((SELECT  combadv_id FROM &erp.vrt_vcombadv v WHERE COMBADV_TYP='S' AND v.GENE_COD=noment.gene_cod AND v.gene_ind=noment.gene_ind),0) combadv_id,
         Nvl(unfab_cod,'9099') unfab_cod,
         ARTENT_IND artent_ind,
         GENE_COD gene_cod,
         GENE_IND gene_ind,
         0 enreg_id,
         To_Date('31/12/1967','dd/mm/yyyy')+Nvl(DEB_DAT,0) deb_dat,
         NULL fin_dat,
         Nvl(ARTENT_LIB,Nvl((SELECT comp_lib FROM &erp.art_comp c WHERE c.gene_cod=noment.gene_cod AND c.gene_ind=noment.gene_ind AND c.complet_typ='G'),'.')) artent_lib,
         NULL fit_cod,
         'N' enreg_s  
  FROM uv_pnomartent_uv noment , uv_pproduits p 
  WHERE p.id=noment.gene_cod || noment.gene_ind
    AND (p.class IS NULL OR p.class ='FICT' OR p.class='LYE')

 );

 /*suppression des nomenclatures ou la combadv n'a pas \ufffdt\ufffd trouv\ufffde*/
 DELETE FROM se_NOM_ARTENT WHERE combadv_id =0;

 UPDATE se_NOM_ARTENT tf SET enreg_id=(select enreg_id FROM &erp.NOM_ARTENT f WHERE f.combadv_id=tf.combadv_id AND f.unfab_cod=tf.unfab_cod AND f.ARTENT_IND=tf.ARTENT_IND ) ;
 UPDATE se_NOM_ARTENT tf SET enreg_s=Nvl((select enreg_s FROM &erp.NOM_ARTENT f WHERE f.combadv_id=tf.combadv_id AND f.unfab_cod=tf.unfab_cod AND f.ARTENT_IND=tf.ARTENT_IND ),'N') ;
 UPDATE se_NOM_ARTENT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 DELETE FROM se_NOM_ARTENT WHERE unfab_cod='0000'   ;
 --SELECT Count(unfab_cod) FROM se_NOM_ARTENT n WHERE NOT EXISTS (SELECT 1 FROM &erp.par_unfab u WHERE u.unfab_cod=n.unfab_cod)

 CALL uvse_synchroverserp('combadv_id@unfab_cod@artent_ind','NOM_ARTENT','O') ;


 commit;            



select * from (
SELECT &outils.stragg(source) source, combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
FROM (
  SELECT 'uv' source ,g.* FROM se_NOM_ARTENT g where unfab_cod != 'ETUDE'
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.NOM_ARTENT g where unfab_cod != 'ETUDE'
  )
where unfab_cod !='ETUDE'
GROUP BY combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
HAVING Count(1)!=2 ) ;


                            